package com.zsyc.util;

import java.sql.*;
import java.util.logging.Logger;
import java.util.logging.Level;

/**
 * 数据库管理类，负责SQLite数据库的连接和初始化
 */
public class DatabaseManager {
    private static final Logger LOGGER = Logger.getLogger(DatabaseManager.class.getName());
    private static final String DB_URL = "jdbc:sqlite:zsyc_learning.db";
    private static Connection connection;
    
    static {
        try {
            // 确保SQLite驱动被加载
            Class.forName("org.sqlite.JDBC");
            LOGGER.info("SQLite驱动加载成功");
        } catch (ClassNotFoundException e) {
            LOGGER.log(Level.SEVERE, "SQLite驱动加载失败", e);
        }
    }
    
    /**
     * 获取数据库连接
     */
    public static Connection getConnection() throws SQLException {
        if (connection == null || connection.isClosed()) {
            connection = DriverManager.getConnection(DB_URL);
            connection.setAutoCommit(true);
        }
        return connection;
    }
    
    /**
     * 初始化数据库，创建必要的表
     */
    public static void initializeDatabase() {
        try (Connection conn = getConnection()) {
            createTables(conn);
            LOGGER.info("数据库初始化完成");
        } catch (SQLException e) {
            LOGGER.log(Level.SEVERE, "数据库初始化失败", e);
            throw new RuntimeException("数据库初始化失败", e);
        }
    }
    
    /**
     * 创建数据库表
     */
    private static void createTables(Connection conn) throws SQLException {
        // 创建单词表
        String createWordTable = """
            CREATE TABLE IF NOT EXISTS word (
                word_id INTEGER PRIMARY KEY AUTOINCREMENT,
                english_word TEXT NOT NULL UNIQUE,
                chinese_meaning TEXT NOT NULL,
                created_at TEXT DEFAULT (datetime('now', 'localtime'))
            )
        """;
        
        // 创建代码题目表
        String createCodeQuestionTable = """
            CREATE TABLE IF NOT EXISTS code_question (
                question_id INTEGER PRIMARY KEY AUTOINCREMENT,
                language TEXT NOT NULL,
                code_snippet TEXT NOT NULL,
                correct_answer TEXT NOT NULL,
                explanation TEXT NOT NULL,
                error_type TEXT NOT NULL,
                difficulty TEXT DEFAULT '初级',
                category TEXT DEFAULT '基础语法',
                created_at TEXT DEFAULT (datetime('now', 'localtime'))
            )
        """;
        
        // 创建任务表
        String createTaskTable = """
            CREATE TABLE IF NOT EXISTS task (
                task_id INTEGER PRIMARY KEY AUTOINCREMENT,
                title TEXT NOT NULL,
                description TEXT,
                due_date DATE,
                category TEXT NOT NULL,
                is_completed BOOLEAN DEFAULT FALSE,
                enable_reminder BOOLEAN DEFAULT FALSE,
                reminder_time INTEGER DEFAULT 2,
                reminder_unit TEXT DEFAULT '小时',
                created_at TEXT DEFAULT (datetime('now', 'localtime'))
            )
        """;
        
        try (Statement stmt = conn.createStatement()) {
            stmt.execute(createWordTable);
            stmt.execute(createCodeQuestionTable);
            stmt.execute(createTaskTable);
        }
    }
    
    /**
     * 重新创建数据库表
     */
    public static void recreateTables() {
        try (Connection conn = getConnection()) {
            // 删除现有表
            try (Statement stmt = conn.createStatement()) {
                stmt.execute("DROP TABLE IF EXISTS task");
                stmt.execute("DROP TABLE IF EXISTS word");
                stmt.execute("DROP TABLE IF EXISTS code_question");
            }
            // 重新创建表
            createTables(conn);
            LOGGER.info("数据库表重新创建完成");
        } catch (SQLException e) {
            LOGGER.log(Level.SEVERE, "重新创建数据库表失败", e);
            throw new RuntimeException("重新创建数据库表失败", e);
        }
    }
    
    /**
     * 关闭数据库连接
     */
    public static void closeConnection() {
        try {
            if (connection != null && !connection.isClosed()) {
                connection.close();
            }
        } catch (SQLException e) {
            LOGGER.log(Level.WARNING, "关闭数据库连接失败", e);
        }
    }
}
